<?php

namespace app\index\controller;

use excel\ExcelDownload;
use excel\ExcelReader;
use PHPExcel;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Border;
use PHPExcel_Style_Fill;
use PHPExcel_Writer_Excel5;
use think\Db;
use think\Validate;

class Ashippingterminal extends Common
{
    //所有导购销售目标管理
    public function index()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $count = Db::table('bx_guide_sales_target_log')->alias('a')
            ->join('employee b', 'a.applyUser_id = b.id')
            ->where($where)
            ->count();
        $this->assign('count', $count);
        //分页
        $maxpage = ceil($count / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_guide_sales_target_log')->alias('a')
            ->join('employee b', 'a.applyUser_id = b.id')
            ->field('a.*,b.em_name user')
            ->where($where)->order('a.id', 'desc')
            ->limit($start, $list)
            ->select();
        $this->assign('data', $data);
        return $this->fetch();
    }

    //替换_
    public function str($where)
    {
        $tmp = [];
        $like = [
            'a_no' => ['like', '%val%'],
            'a_state' => ['like', '%val%'],
            'a_year' => ['like', '%val%'],
            'a_theme' => ['like', '%val%'],
            'b_em_name' => ['like', '%val%'],
            'b_title' => ['like', '%val%'],
            'p_title' => ['like', '%val%'],
            'n_title' => ['like', '%val%'],
            'o_title' => ['like', '%val%'],
            'c_title' => ['like', '%val%'],
            'c_name' => ['like', '%val%'],
            'c_IDNo' => ['like', '%val%'],
            'l_category_name' => ['like', '%val%'],
            'pa_title' => ['like', '%val%'],
            'na_title' => ['like', '%val%'],
            'oa_title' => ['like', '%val%'],
            'qa_title' => ['like', '%val%'],
            'm_title' => ['like', '%val%'],
            'j_title' => ['like', '%val%'],
            'b_year' => ['like', '%val%'],
            'a_kh1' => ['like', '%val%'],
            'a_kh2' => ['like', '%val%'],
            'z_cp_dimension' => ['like', '%val%'],
            'i_brand_name' => ['like', '%val%'],
            'pages' => 'val',
            'limts' => 'val',

        ];
        foreach ($where as $key => $val) {
            if (!empty($val)) {
                if (isset($where['a_endDate']) && ($where['a_endDate'] == 1)) {
                    $tmp[] = ['exp', '(DATE_SUB(`endDate`,INTERVAL 2 MONTH) < CURDATE()) AND (CURDATE() <= `endDate`)'];
                    unset($where['a_endDate']);
                }
                if (isset($where['a_fyDate']) && isset($where['a_fyDate1'])) {
                    if ($where['a_fyDate'] && $where['a_fyDate1']) {
                        $tmp['a.signDate'] = ['between', [$where['a_fyDate'], $where['a_fyDate1']]];
                    } else if ($where['a_fyDate']) {
                        $tmp['a.signDate'] = ['>=', $where['a_fyDate']];
                    } else if ($where['a_fyDate1']) {
                        $tmp['a.signDate'] = ['<=', $where['a_fyDate1']];
                    }
                }
                foreach ($like as $k => $v) {
                    if ($key == $k) {
                        $tmp[preg_replace('/\_/', '.', $key, 1)] = str_replace('val', $val, $v);
                    }
                }

            }
            unset($tmp['items']);

        }
        return $tmp;
    }

    //导入导购销售目标格式
    public function import()
    {
        $year = Db::name('bx_guide_format')->column('year');
        $this->assign('year', $year);
        return $this->fetch();
    }

    //form
    public function importform()
    {
        $data = $this->request->post();
        $file = request()->file('file');
        // 移动到框架应用根目录/public/uploads/bx_guide 目录下
        $info = $file->validate(['ext' => 'xls,xlsx', 'size' => 105678])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide');
        if ($info) {
            // 成功上传后 获取上传信息
            $tmp = self::testExcel($info->getSaveName(), $data);
            if ($tmp['success'] == 'success') {
                echo "<script>top.sweetAlert('" . $tmp['msg'] . "','','" . $tmp['success'] . "');history.go(-1);</script>";
            } else {
                echo "<script>top.sweetAlert('" . $tmp['msg'] . "','','" . $tmp['success'] . "');</script>";
            }
        } else {
            // 上传失败获取错误信息
            echo "<script>top.sweetAlert('" . $file->getError() . "','','warning');</script>";
        }
    }

    /**
     * excel导入示例
     */
    public
    function testExcel($file, $tmpdata)
    {
        $tmp = Db::table('bx_guide_format')->where('year', $tmpdata['year'])->find();
        $titlesMap = ['name' => '导购员姓名'];
        if ($tmp['wd_dimension']) {
            $titlesMap = array_merge($titlesMap, ['wd_id' => '网点名称']);
        }
        $type = ['品牌', '品类', '系列', 'SKU', '单品'];
        $type1 = ['pp_id', 'pl_id', 'xl_id', 'sku_id', 'cp_id'];
        if ($tmp['cp_dimension']) {
            $titlesMap = array_merge($titlesMap, [$type1[$tmp['cp_type']] => '产品' . $type[$tmp['cp_type']]]);
        }
        $kh = ['一级考核期', '二级考核期'];//kh_dimension
        $kh1 = ['kh1', 'kh2'];
        $titlesMap = array_merge($titlesMap, [$kh1[$tmp['kh_dimension']] => $kh[$tmp['kh_dimension']]]);
        $titlesMap = array_merge($titlesMap, ['money' => '销售目标金额(元)']);
        //表格title
//        $titlesMap = [
//            'guideName' => '导购岗位名称',
//            'sort' => '显示次序',
//        ];
        $url = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide' . DS . $file;
        $reader = new ExcelReader($url);
        // 是否提供titlesMap 返回的数据结构不一致
        $data = $reader->exec($titlesMap);
        if ($data) {
            unset($data[0]);
            foreach ($data as $key => $val) {
                $is_empty = 0;
                foreach ($val as $k => $v) {
                    if (!trim($v)) {
                        $is_empty++;
                    }
                }
                if ($is_empty == count($val)) unset($data[$key]);
            }
            if ($data) {
                $name = Db::table('bx_guide_user')->column('id,name');
                $name1 = implode(',', $name);
                $vali = [
                    ['name', 'require|in:' . $name1, '导购员必须|导购员不存在'],
                    ['money', 'require|number', '销售目标金额必须|销售目标金额格式']
                ];
                $wd = Db::table('cg_net')->column('id,title');
                if ($tmp['wd_dimension']) {
                    $wd1 = implode(',', $wd);
                    $vali[] = ['wd_id', 'require|in:' . $wd1, '网点必须不存在|网点'];
                }
                $cpTable = ['pro_brands', 'pro_category', 'pro_series', 'pro_sku', 'pro_products'];
                $field = ['brand_name', 'category_name', 'series_name', 'sku_name', 'pro_name'];
                $cp = Db::table($cpTable[$tmp['cp_type']])->column('id,' . $field[$tmp['cp_type']]);
                if ($tmp['cp_dimension']) {
                    $cp1 = implode(',', $cp);
                    $vali[] = [$type1[$tmp['cp_type']], 'require|in:' . $cp1, $type[$tmp['cp_type']] . '必须|' . $type[$tmp['cp_type']] . '不存在'];
                }
                //财年
                $cn = implode(',', Db::table('bx_guide_format')->column('year'));
                $vali[] = [$kh1[$tmp['kh_dimension']], 'require|in:' . $cn, $kh[$tmp['kh_dimension']] . '必须|' . $kh[$tmp['kh_dimension']] . '不存在'];
                $validate = new Validate($vali);
                $off = 1;
                foreach ($data as $key => $val) {
                    $data[$key]['createDate'] = date('Y-m-d');
                    $data[$key]['createUser_id'] = session('employee_id');
                    if (isset($val['wd_id'])) {
                        $data[$key]['wd_id'] = array_search($val['wd_id'], $wd);
                    }
                    $data[$key]['name_id'] = array_search($val['name'], $name);
                    $data[$key][$type1[$tmp['cp_type']]] = array_search($val[$type1[$tmp['cp_type']]], $cp);
                    if ($validate->check($val)) {
                        $off = 1;
                    } else {
                        $off = 0;
                        break;
                    }
                }
                if ($off === 1) {
//                    $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
                    if (!isset($tmpdata['oldID'])) $tmpdata['oldID'] = NULL;
                    if (!isset($tmpdata['oldNo'])) $tmpdata['oldNo'] = NULL;
                    $log = [
                        'no' => date('Ymd') . '00' . rand('0', '999'),
                        'theme' => $tmpdata['theme'] . $tmpdata['year'],
                        'state' => $tmpdata['state'],
                        'year' => $tmpdata['year'],
                        'applyDate' => date('Y-m-d'),
                        'applyUser_id' => session('employee_id'),
                        'oldID' => $tmpdata['oldID'],
                        'oldNo' => $tmpdata['oldNo'],
                    ];
                    if ($id = Db::table('bx_guide_sales_target_log')->insertGetId($log)) {
                        foreach ($data as $key => $val) {
                            $data[$key]['sales_target_log_id'] = $id;
                        }
                        if (Db::table('bx_guide_sales_target')->insertAll($data)) {
                            return [
                                "success" => 'success',
                                'msg' => '上传文件成功'
                            ];
                        } else {
                            return [
                                "success" => 'warning',
                                'msg' => '网络错误！请重试'
                            ];
                        }
                    } else {
                        return [
                            "success" => 'warning',
                            'msg' => '网络错误！请重试'
                        ];
                    }
                } else {
                    return [
                        "success" => 'warning',
                        "msg" => $validate->getError()
                    ];
                }
            } else {
                return [
                    "success" => 'warning',
                    'msg' => '请填充数据'
                ];
            }
        } else {
            return [
                "success" => 'warning',
                'msg' => '网络错误！请重试'
            ];
        }
    }


    /**
     * @param $data //数组
     */
    public function makeExcel()
    {
        $year = $this->request->get('year');
        if ($year) {
            $data = Db::table('bx_guide_format')->where('year', $year)->find();
            $title[] = '导购员姓名';
            $content[] = '必填项。';
            if ($data['wd_dimension']) {
                $title[] = '网点名称';
                $content[] = '必填项。必须是销路通系统中已存在的网点名称';
            }
            if ($data['cp_dimension']) {
                $type = ['品牌', '品类', '系列', 'SKU', '单品'];
                $title[] = '产品' . $type[$data['cp_type']];
                $content[] = '必填项。必须是销路通系统中已存在的产品' . $type[$data['cp_type']];
            }
            $kh = ['一级考核期', '二级考核期'];//kh_dimension
            $title[] = $kh[$data['kh_dimension']];
            $content[] = '必填项。必须是销路通系统中已存在的' . $kh[$data['kh_dimension']] . '，且该' . $kh[$data['kh_dimension']] . '属于财年';
            $title[] = '销售目标金额(元)';
            $content[] = '必填项';
            //表格title
            $excel = ['title' => $title, 'content' => $content];
            $this->excel($excel);
        } else {
            echo '主键错误！';
        }
    }

    //导出excel
    public function excel($data)
    {


        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
//        $objPHPExcel->getActiveSheet()->setTitle($title);

        for ($i = 0; $i < count($data["content"]); $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue(chr($i + 97) . '1', $data["title"][$i]);

            //加粗
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getFont()->setBold(true);
            //水平剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //填充背景色
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getFill()->getStartColor()->setARGB('00ff3322');

            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '1')->getAlignment()->setWrapText(true);


            $objPHPExcel->getActiveSheet()->setCellValue(chr($i + 97) . '2', $data["content"][$i]);
            //水平剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

            //填充背景色
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '2')->getFill()->getStartColor()->setARGB('00FFFF00');

            $objPHPExcel->getActiveSheet()->getStyle(chr($i + 97) . '2')->getAlignment()->setWrapText(true);
            //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension(chr($i + 97))->setWidth(30);

        }

        //设置高度
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(50);
        for ($i = 0; $i <= 20; $i++) {
            $h = 'A' . ($i + 3);
            $objPHPExcel->getActiveSheet()->setCellValue($h, " ");
        }
        //***********************画出单元格边框*****************************
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    //'color' => array('argb' => 'FFFF0000'),
                ),
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle('A1:' . chr(96 + count($data["content"])) . '2')->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪个格就把这个N改为那个字母替代
//***********************画出单元格边框结束*****************************
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        //var_dump($objWriter);exit;
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }


    //所有导购销售目标查询
    public function query()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $count = Db::table('bx_guide_sales_target_log')->alias('a')
            ->join('employee b', 'a.applyUser_id = b.id')
            ->where($where)
            ->count();
        $this->assign('count', $count);
        //分页
        $maxpage = ceil($count / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_guide_sales_target_log')->alias('a')
            ->join('employee b', 'a.applyUser_id = b.id')
            ->field('a.*,b.em_name user')
            ->where($where)->order('id', 'desc')
            ->limit($start, $list)
            ->select();
        $this->assign('data', $data);
        return $this->fetch();
    }

    //详情
    public function info()
    {
        $id = $this->request->get('id');
        if ($id) {
            $data = Db::table('bx_guide_sales_target_log')->alias('a')
                ->join('employee b', 'a.applyUser_id = b.id')
                ->field('a.*,b.em_name user')->where('a.id', $id)->find();
            $tmp = Db::table('bx_guide_sales_target')->alias('a')
                ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
                ->join('cg_net c', 'a.wd_id = c.id', 'LEFT')
                ->join('pro_brands d', 'a.pp_id = d.id', 'LEFT')
                ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
                ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
                ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
                ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')
                ->field('a.name,c.title wd,b.year,d.brand_name pp,e.category_name pl,
                f.series_name xl,g.sku_name sku,h.pro_name cp,a.kh1,a.kh2,a.money')
                ->where('a.sales_target_log_id', $id)
                ->select();
            $title = [
                'name' => '导购员',
                'wd' => '网点',
                'year' => '财年',
                'pp' => '产品品牌',
                'pl' => '产品品类',
                'xl' => '产品系列',
                'sku' => '产品SKU',
                'cp' => '产品单品',
                'kh1' => '一级考核期',
                'kh2' => '二级考核期',
                'money' => '销售目标（元）',
            ];
            $titles = [];
            if ($tmp) {
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        if (!trim($v)) {
                            unset($tmp[$key][$k]);
                        }
                    }
                }
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        $titles[$k] = $title[$k];
                    }
                }
            }
            if (!$titles) {
                $titles = [
                    'name' => '导购员',
                    'wd' => '网点',
                    'year' => '财年',
                    'pp' => '产品品牌',
                    'kh1' => '一级考核期',
                    'money' => '销售目标（元）',
                ];
            }
            $this->assign('title', $titles);
            $this->assign('data', $data);
            $this->assign('tmp', $tmp);
            return $this->fetch();
        } else {
            echo '主键缺失！';
        }
    }


    //异动
    public function edit()
    {
        $id = $this->request->get('id');
        if ($id) {
            $data = Db::table('bx_guide_sales_target_log')
                ->where('id', $id)->find();
            $tmp = Db::table('bx_guide_sales_target')->alias('a')
                ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
                ->join('cg_net c', 'a.wd_id = c.id', 'LEFT')
                ->join('pro_brands d', 'a.pp_id = d.id', 'LEFT')
                ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
                ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
                ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
                ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')
                ->field('a.name,c.title wd,b.year,d.brand_name pp,e.category_name pl,
                f.series_name xl,g.sku_name sku,h.pro_name cp,a.kh1,a.kh2,a.money')
                ->where('a.sales_target_log_id', $id)
                ->select();
            $title = [
                'name' => '导购员',
                'wd' => '网点',
                'year' => '财年',
                'pp' => '产品品牌',
                'pl' => '产品品类',
                'xl' => '产品系列',
                'sku' => '产品SKU',
                'cp' => '产品单品',
                'kh1' => '一级考核期',
                'kh2' => '二级考核期',
                'money' => '销售目标（元）',
            ];
            $titles = [];
            if ($tmp) {
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        if (!trim($v)) {
                            unset($tmp[$key][$k]);
                        }
                    }
                }
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        $titles[$k] = $title[$k];
                    }
                }
            }
            if (!$titles) {
                $titles = [
                    'name' => '导购员',
                    'wd' => '网点',
                    'year' => '财年',
                    'pp' => '产品品牌',
                    'kh1' => '一级考核期',
                    'money' => '销售目标（元）',
                ];
            }
            $this->assign('title', $titles);
            $this->assign('data', $data);
            $this->assign('tmp', $tmp);
            return $this->fetch();
        } else {
            echo '主键缺失！';
        }
    }

    //导出导购销售目标明细数据
    public function export()
    {
        $id = $this->request->get('id');
        if ($id) {
            $tmp = Db::table('bx_guide_sales_target')->alias('a')
                ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
                ->join('cg_net c', 'a.wd_id = c.id', 'LEFT')
                ->join('pro_brands d', 'a.pp_id = d.id', 'LEFT')
                ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
                ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
                ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
                ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')
                ->field('a.name,c.title wd,b.year,d.brand_name pp,e.category_name pl,
                f.series_name xl,g.sku_name sku,h.pro_name cp,a.kh1,a.kh2,a.money')
                ->where('a.sales_target_log_id', $id)
                ->select();
            $title = [
                'name' => '导购员',
                'wd' => '网点',
                'year' => '财年',
                'pp' => '产品品牌',
                'pl' => '产品品类',
                'xl' => '产品系列',
                'sku' => '产品SKU',
                'cp' => '产品单品',
                'kh1' => '一级考核期',
                'kh2' => '二级考核期',
                'money' => '销售目标（元）',
            ];
            $titles = [];
            if ($tmp) {
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        if (!trim($v)) {
                            unset($tmp[$key][$k]);
                        }
                    }
                }
                foreach ($tmp as $key => $val) {
                    foreach ($val as $k => $v) {
                        $titles[$k] = $title[$k];
                    }
                }
            }
            if (!$titles) {
                $titles = [
                    'name' => '导购员',
                    'wd' => '网点',
                    'year' => '财年',
                    'pp' => '产品品牌',
                    'kh1' => '一级考核期',
                    'money' => '销售目标（元）',
                ];
            }
            $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
            $str = '注：本表单由销路通软件导出，导出时间：' . date('Y-m-d H:i:s') . '；导出人：' . $user;
            $str1 = '导购销售目标明细数据';
            $tmp[] = [
                'title' => $str1,
                'sub' => $str,
            ];
            $excel = new ExcelDownload();
            $excel->exec($tmp, $titles, '导购销售目标明细数据', ['sheet' => ['rowHeight' => 20]], 3);
        } else {
            echo '主键缺失！';
        }
    }


    //所有导购销售目标统计
    public function statistics()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $count = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id = c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->where($where)
            ->count();
        $this->assign('count', $count);
        //分页
        $maxpage = ceil($count / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id == c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->field("a.id,concat(b.no,'_',a.id) info_no,c.name,b.year,a.kh1,a.kh2,d.title wd,
            i.brand_name pp,e.category_name pl,f.series_name xl,g.sku_name sku,h.pro_name,a.money,c.workNumber,c.IDNo,(case
            when c.postType = 0 then '全职'
            when c.postType = 1 then '兼职'
            when c.postType = 2 then '专职'
            end) postType,(case 
            when c.guideGrade = 0 then '金牌导购'
            when c.guideGrade = 1 then '银牌导购'
            when c.guideGrade = 2 then '普通导购'
            end) guideGrade,c.entryDate,c.justDate,c.phone,j.sn wangdianno,j.title wangdian,
            j.mysn,if(j.direct = 0,'否','是') direct,k.brand_name pinpai,l.category_name pinlei,
            n.title yj,p.title ej,ha.title s,ma.title shi,pa.title yq,na.title ys,oa.title yshi,qa.sn jxs_no,qa.title jxs,m.sn xt_no,m.title xt,
            pra.em_name pr,nra.em_name nr,aba.em_name ab,
            qa.stuff jxsfz,m.stuff xtfz,j.stuff wdfz")
            ->where($where)->order('a.id', 'desc')
            ->limit($start, $list)
            ->select();
        foreach ($data as $key => $val) {
            if ($val['jxsfz']) {
                $data[$key]['jxsfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['jxsfz'])) . ")")->column('em_name'));
            }
            if ($val['wdfz']) {
                $data[$key]['wdfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['wdfz'])) . ")")->column('em_name'));
            }
            if ($val['xtfz']) {
                $data[$key]['xtfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['xtfz'])) . ")")->column('em_name'));
            }
        }

        $this->assign('data', $data);
        //一级考核期
        $kh1 = Db::table('bx_guide_sales_target')->column('kh1');
        $kh2 = Db::table('bx_guide_sales_target')->column('kh2');
        $this->assign('kh1', array_filter($kh1));
        $this->assign('kh2', array_filter($kh2));
        return $this->fetch();
    }

    public function exportStatistics()
    {
        $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $str = '注：本表单由销路通软件导出，导出时间：' . date('Y-m-d H:i:s') . '；导出人：' . $user;
        $str1 = '导购档案汇总';
//        $titles = ['姓名', '年龄', '性别', '身高'];
        // titles格式也可以如下
        $titles = [
            'fileNo' => '明细编号',
            'workNumber' => '导购员',
            'name' => '财年',
            'nation' => '一级考核期',
            'sex' => '二级考核期',
            'jiguan' => '销售网点',
            'birthDate' => '销售品牌',
            'hunyinState' => '销售品类',
            'zinvSate' => '销售系列',
            'phone' => '销售SKU',
            'email' => '销售单品',
            'otherPhone' => '目标金额（元）',
            'IDNo' => '导购工号',
            'IDNoDate' => '导购身份证',
            'IDNoOffice' => '岗位类型',
            'IDNoAddress' => '导购等级',
            'houseType' => '入职时间',
            'houseAddress' => '离职时间',
            'livingAddress' => '手机号',
            'workAddress' => '网点编号',
            'workAddress1' => '网点名称',
            'openingBank' => '系统自编店号',
            'bankAccount' => '是否直营',
            'urgentLinkman' => '品牌',
            'urgentTel' => '品类',
            'urgentAddress' => '一级渠道',
            'postalCode' => '二级渠道',
            'entryDate' => '网点所属行政省份',
            'justDate' => '网点所属行政城市',
            'department' => '网点所属营销分区',
            'post' => '网点所属营销省份',
            'firstBoss' => '网点所属营销城市',
            'duiyngshengfen' => '经销商编号',
            'postType' => '经销商',
            'fanPin' => '系统编号',
            'nowState' => '系统',
            'pinyongType' => '分区经理',
            'signContract' => '省份经理',//1是，0否
            'referee' => '城市经理',//referee_id
            'recruitmentChannels' => '经销商负责人',
            'isDanbao' => '系统负责人',
            'zuigaoxueli' => '网点负责人',
        ];
        $data = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id = c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->field("concat(b.no,'_',a.id) info_no,c.name,b.year,a.kh1,a.kh2,d.title wd,
            i.brand_name pp,e.category_name pl,f.series_name xl,g.sku_name sku,h.pro_name,a.money,c.workNumber,c.IDNo,(case
            when c.postType = 0 then '全职'
            when c.postType = 1 then '兼职'
            when c.postType = 2 then '专职'
            end) postType,(case 
            when c.guideGrade = 0 then '金牌导购'
            when c.guideGrade = 1 then '银牌导购'
            when c.guideGrade = 2 then '普通导购'
            end) guideGrade,c.entryDate,c.justDate,c.phone,j.sn wangdianno,j.title wangdian,
            j.mysn,if(j.direct = 0,'否','是') direct,k.brand_name pinpai,l.category_name pinlei,
            n.title yj,p.title ej,ha.title s,ma.title shi,pa.title yq,na.title ys,oa.title yshi,qa.sn jxs_no,qa.title jxs,m.sn xt_no,m.title xt,
            pra.em_name pr,nra.em_name nr,aba.em_name ab,
            qa.stuff jxsfz,m.stuff xtfz,j.stuff wdfz")
            ->select();
        foreach ($data as $key => $val) {
            if ($val['jxsfz']) {
                $data[$key]['jxsfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['jxsfz'])) . ")")->column('em_name'));
            }
            if ($val['wdfz']) {
                $data[$key]['wdfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['wdfz'])) . ")")->column('em_name'));
            }
            if ($val['xtfz']) {
                $data[$key]['xtfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['xtfz'])) . ")")->column('em_name'));
            }
        }
        if ($data) {
            $data[] = [
                'title' => $str1,
                'sub' => $str,
            ];
            $excel = new ExcelDownload();
            $excel->exec($data, $titles, '所有导购销售目标统计', ['sheet' => ['rowHeight' => 20]], 3);
        } else {
            echo "<script>top.sweetAlert('没有数据','','warning');history.go(-1);</script>";
        }

    }

    public function notarget()
    {
        //查询条件
        $where = $this->request->post();
        $this->assign('where', $where);
        $where = self::str($where);
        $page = empty($where['pages']) ? 1 : $where['pages'];
        $list = empty($where['limts']) ? 10 : $where['limts'];
        unset($where['pages']);
        unset($where['limts']);
        //总数量，总金额，总条数
        $count = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id != c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->where($where)
            ->group('c.id')
            ->count();
        $this->assign('count', $count);
        //分页
        $maxpage = ceil($count / $list);
        $page = $page < $maxpage ? $page : $maxpage;
        $page = $page > 1 ? $page : 1;
        $start = ($page - 1) * $list;
        $this->assign('page', $page);
        $this->assign('maxpage', $maxpage);
        $this->assign('limit', $list);
        //数据
        $data = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id != c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->field("a.id,c.workNumber,c.name,
            c.IDNo,(case
            when c.postType = 0 then '全职'
            when c.postType = 1 then '兼职'
            when c.postType = 2 then '专职'
            end) postType,(case 
            when c.guideGrade = 0 then '金牌导购'
            when c.guideGrade = 1 then '银牌导购'
            when c.guideGrade = 2 then '普通导购'
            end) guideGrade,c.entryDate,c.justDate,c.phone,j.sn wangdianno,j.title wangdian,
            j.mysn,if(j.direct = 0,'否','是') direct,k.brand_name pinpai,l.category_name pinlei,
            n.title yj,p.title ej,ha.title s,ma.title shi,pa.title yq,na.title ys,oa.title yshi,qa.sn jxs_no,qa.title jxs,m.sn xt_no,m.title xt,
            pra.em_name pr,nra.em_name nr,aba.em_name ab,
            qa.stuff jxsfz,m.stuff xtfz,j.stuff wdfz")
            ->group('c.id')
            ->where($where)->order('a.id', 'desc')
            ->limit($start, $list)
            ->select();
        foreach ($data as $key => $val) {
            if ($val['jxsfz']) {
                $data[$key]['jxsfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['jxsfz'])) . ")")->column('em_name'));
            }
            if ($val['wdfz']) {
                $data[$key]['wdfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['wdfz'])) . ")")->column('em_name'));
            }
            if ($val['xtfz']) {
                $data[$key]['xtfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['xtfz'])) . ")")->column('em_name'));
            }
        }

        $this->assign('data', $data);
        //一级考核期
        $kh1 = Db::table('bx_guide_sales_target')->column('kh1');
        $kh2 = Db::table('bx_guide_sales_target')->column('kh2');
        $this->assign('kh1', array_filter($kh1));
        $this->assign('kh2', array_filter($kh2));
        return $this->fetch();
    }

    public function exportNotarget()
    {
        $user = Db::table('employee')->where('id', session('employee_id'))->value('em_name');
        $str = '注：本表单由销路通软件导出，导出时间：' . date('Y-m-d H:i:s') . '；导出人：' . $user;
        $str1 = '导购档案汇总';
//        $titles = ['姓名', '年龄', '性别', '身高'];
        // titles格式也可以如下
        $titles = [
            'fileNo' => '未设置目标考核期',
            'IDNo' => '导购工号',
            'workNumber' => '导购员',
            'IDNoDate' => '导购身份证',
            'IDNoOffice' => '岗位类型',
            'IDNoAddress' => '导购等级',
            'houseType' => '入职时间',
            'houseAddress' => '离职时间',
            'livingAddress' => '手机号',
            'workAddress' => '网点编号',
            'workAddress1' => '网点名称',
            'openingBank' => '系统自编店号',
            'bankAccount' => '是否直营',
            'urgentLinkman' => '品牌',
            'urgentTel' => '品类',
            'urgentAddress' => '一级渠道',
            'postalCode' => '二级渠道',
            'entryDate' => '网点所属行政省份',
            'justDate' => '网点所属行政城市',
            'department' => '网点所属营销分区',
            'post' => '网点所属营销省份',
            'firstBoss' => '网点所属营销城市',
            'duiyngshengfen' => '经销商编号',
            'postType' => '经销商',
            'fanPin' => '系统编号',
            'nowState' => '系统',
            'pinyongType' => '分区经理',
            'signContract' => '省份经理',
            'referee' => '城市经理',
            'recruitmentChannels' => '经销商负责人',
            'isDanbao' => '系统负责人',
            'zuigaoxueli' => '网点负责人',
        ];
        $data = Db::table('bx_guide_sales_target')->alias('a')
            ->join('bx_guide_sales_target_log b', 'a.sales_target_log_id = b.id')
            ->join('bx_guide_user c', 'a.name_id != c.id')
            ->join('cg_net d', 'a.wd_id = d.id', 'LEFT')
            ->join('pro_brands i', 'a.pp_id = i.id', 'LEFT')
            ->join('pro_category e', 'a.pl_id = e.id', 'LEFT')
            ->join('pro_series f', 'a.xl_id = f.id', 'LEFT')
            ->join('pro_sku g', 'a.sku_id = g.id', 'LEFT')
            ->join('pro_products h', 'a.cp_id = h.id', 'LEFT')//---到此为止，目标信息
            ->join('cg_net j', 'c.mainNet_id = j.id', 'LEFT')//---到此为止，目标信息
            ->join('pro_brands k', 'c.pinpai_id = k.id', 'LEFT')
            ->join('pro_category l', 'c.pinlei_id = l.id', 'LEFT')
            ->join('cg_system m', 'j.system_id = m.id', 'LEFT')//系统表
            ->join('cg_channel_types1 n', 'm.oneCType = n.id', 'LEFT')//一级渠道表
            ->join('cg_channel_types2 p', 'm.twoCType = p.id', 'LEFT')//二级渠道表
            ->join('cg_provice ha', 'j.exec_provice = ha.id', 'LEFT')//行政省
            ->join('cg_city ma', 'j.exec_city = ma.id', 'LEFT')//行政市
            ->join('cg_market_provice na', 'j.provice = na.id', 'LEFT')//营销省
            ->join('employee nra', 'na.head = nra.id', 'LEFT')
            ->join('cg_market_city oa', 'j.city = oa.id', 'LEFT')//营销市
            ->join('employee aba', 'oa.head = aba.id', 'LEFT')
            ->join('cg_market_zoning pa', 'j.zoning = pa.id', 'LEFT')//营销区
            ->join('employee pra', 'pa.head = pra.id', 'LEFT')
            ->join('cg_jxs qa', 'j.jxs = qa.id', 'LEFT')//经销商
            ->join('bx_guide_format z', 'b.year = z.year', 'LEFT')//经销商
            ->field("c.workNumber,c.name,
            c.IDNo,(case
            when c.postType = 0 then '全职'
            when c.postType = 1 then '兼职'
            when c.postType = 2 then '专职'
            end) postType,(case 
            when c.guideGrade = 0 then '金牌导购'
            when c.guideGrade = 1 then '银牌导购'
            when c.guideGrade = 2 then '普通导购'
            end) guideGrade,c.entryDate,c.justDate,c.phone,j.sn wangdianno,j.title wangdian,
            j.mysn,if(j.direct = 0,'否','是') direct,k.brand_name pinpai,l.category_name pinlei,
            n.title yj,p.title ej,ha.title s,ma.title shi,pa.title yq,na.title ys,oa.title yshi,qa.sn jxs_no,qa.title jxs,m.sn xt_no,m.title xt,
            pra.em_name pr,nra.em_name nr,aba.em_name ab,
            qa.stuff jxsfz,m.stuff xtfz,j.stuff wdfz")
            ->group('c.id')
            ->select();
        foreach ($data as $key => $val) {
            array_unshift($data[$key], '未设置目标考核期');
            if ($val['jxsfz']) {
                $data[$key]['jxsfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['jxsfz'])) . ")")->column('em_name'));
            }
            if ($val['wdfz']) {
                $data[$key]['wdfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['wdfz'])) . ")")->column('em_name'));
            }
            if ($val['xtfz']) {
                $data[$key]['xtfz'] = implode(',', Db::name('employee')->where("id in (" . implode(',', unserialize($val['xtfz'])) . ")")->column('em_name'));
            }
        }
        if ($data) {
            $data[] = [
                'title' => $str1,
                'sub' => $str,
            ];
            $excel = new ExcelDownload();
            $excel->exec($data, $titles, '所有未设置目标的导购名单', ['sheet' => ['rowHeight' => 20]], 3);
        } else {
            echo "<script>top.sweetAlert('没有数据','','warning');history.go(-1);</script>";
        }

    }

    public function aa()
    {
        $str = 'bx_fy, bx_fy_log, bx_fy_log_des, bx_fy2, bx_fy2_log, bx_guide_accident, bx_guide_apple_log, bx_guide_format, bx_guide_labour_contract, bx_guide_post, bx_guide_quit, bx_guide_reminding, bx_guide_salarylevel, bx_guide_sales_target, bx_guide_sales_target_log, bx_guide_thingtype, bx_guide_user, bx_guide_user_log, bx_guide_wages, bx_guide_wages_log, bx_wd, bx_wd_log';
        $str1 = 'bx_fy, bx_fy_log, bx_fy_log_des, bx_fy2, bx_fy2_log, bx_guide_accident, bx_guide_apple_log, bx_guide_labour_contract, bx_guide_post, bx_guide_quit, bx_guide_reminding, bx_guide_salarylevel, bx_guide_thingtype, bx_guide_user, bx_guide_user_log, bx_guide_wages, bx_guide_wages_log, bx_wd, bx_wd_log';
        $str2 = explode(', ', $str);
        $str3 = explode(', ', $str1);
        $tmp = array_diff($str2, $str3);
        p($tmp);
    }

    //导购排班导入
    public function importscheduling()
    {
        return $this->fetch();
    }

    //form
    public function importschedulingform()
    {
        $data = $this->request->post();
        $file = request()->file('file');
        // 移动到框架应用根目录/public/uploads/bx_guide 目录下
        $info = $file->validate(['ext' => 'xls,xlsx', 'size' => 105678])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide');
        if ($info) {
            // 成功上传后 获取上传信息
            $tmp = self::scheduling($info->getSaveName(), $data);
            if ($tmp['success'] == 'success') {
                echo "<script>top.sweetAlert('" . $tmp['msg'] . "','','" . $tmp['success'] . "');</script>";
            } else {
                echo "<script>top.sweetAlert('" . $tmp['msg'] . "','','" . $tmp['success'] . "');</script>";
            }
        } else {
            // 上传失败获取错误信息
            echo "<script>top.sweetAlert('" . $file->getError() . "','','warning');</script>";
        }
    }

    /**
     * excel导入示例
     */
    public
    function scheduling($file, $tmpdata)
    {
        //表格title
        $titlesMap = [
            'name_id' => '导购员',
            'dateOfShift' => '排班日期',
            'className_id' => '班次名称',
            'workShift' => '上班时间',
            'closingTime' => '下班时间',
            'natureWork_id' => '上班性质',
            'noWorkDate' => '不在岗开始时间',
            'workDate' => '不在岗结束时间',
        ];
        $url = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide' . DS . $file;
        $reader = new ExcelReader($url);
        // 是否提供titlesMap 返回的数据结构不一致
        $data = $reader->exec();
        if ($data) {
            unset($data[0]);
            unset($data[1]);
            unset($data[2]);
            foreach ($data as $key => $val) {
                $is_empty = 0;
                foreach ($val as $k => $v) {
                    if (!trim($v)) {
                        $is_empty++;
                    }
                }
                if ($is_empty == count($val)) unset($data[$key]);
            }
            foreach ($data as $key => $val) {
                $tmp[] = array_combine(array_keys($titlesMap), $val);
            }
            if ($tmp) {
                $name = Db::table('bx_guide_user')->column('id,name');
                $name1 = implode(',', $name);
                //班次名稱
                $className = Db::table('bx_guide_scheduling_set')->column('id,className');
                $className1 = implode(',', $className);
                //上班性质
                $natureWork_id = ['做六休一','做五休二'];
                $natureWork_id1 = implode(',', $natureWork_id);
                $validate = new Validate([
                    ['name_id', 'require|notIn:' . $name1, '导购员必须|导购员不存在'],
                    ['dateOfShift', 'require|dateFormat:Y-m-d', '排班日期必须|排班日期格式 年-月-日'],
                    ['className_id', 'require|in:' . $className1, '班次名称必须|班次名称不存在'],
                    ['workShift', 'require|dateFormat:H:i', '上班时间必须|上班时间格式 时:分'],
                    ['closingTime', 'require|dateFormat:H:i', '下班时间必须|下班时间格式 时:分'],
                    ['natureWork_id', 'require|in:' . $natureWork_id1, '上班性质必须|上班性质不存在'],
                    ['noWorkDate', 'require|dateFormat:H:i', '不在岗开始时间必须|不在岗开始时间格式 时:分'],
                    ['workDate', 'require|dateFormat:H:i', '不在岗结束时间必须|不在岗结束时间格式 时:分'],
                ]);
                $off = 1;
                foreach ($tmp as $key => &$val) {
                    $val['name_id'] = array_search($val['name_id'], $name);
                    $val['className_id'] = array_search($val['className_id'], $className);
                    $val['natureWork_id'] = array_search($val['natureWork_id'], $natureWork_id);
                    if ($validate->check($val)) {
                        $off = 1;
                    } else {
                        $off = 0;
                        break;
                    }
                }
                if ($off === 1) {
                    if (Db::table('bx_guide_scheduling')->insertAll($tmp)) {
                        return [
                            "success" => 'success',
                            'msg' => '上传文件成功'
                        ];
                    } else {
                        return [
                            "success" => 'warning',
                            'msg' => '网络错误！请重试'
                        ];
                    }
                } else {
                    return [
                        "success" => 'warning',
                        "msg" => $validate->getError()
                    ];
                }
            } else {
                return [
                    "success" => 'warning',
                    'msg' => '请填充数据'
                ];
            }
        } else {
            return [
                "success" => 'warning',
                'msg' => '网络错误！请重试'
            ];
        }
    }

    public function test(){
        $agent = strtolower($_SERVER['HTTP_USER_AGENT']);
        //分析数据
        $is_pc = (strpos($agent, 'windows nt')) ? true : false;
        $is_mac = (strpos($agent, 'macintosh')) ? true : false;
        $is_iphone = (strpos($agent, 'iphone')) ? true : false;
        $is_ipad = (strpos($agent, 'ipad')) ? true : false;
        $is_android = (strpos($agent, 'android')) ? true : false;
        $className = Db::table('bx_guide_scheduling_set')->column('id,className');
        $className1 = implode(',',$className);
        p($className1);
    }

    //班次设置
    //Db::table('employee')->where('id', session('employee_id'))->value('em_name');
    public function schedulingset(){
        $data = Db::table('bx_guide_scheduling_set')->alias('a')
        ->join('employee b','a.lastEditUser_id = b.id')
        ->field('a.*,b.em_name')->order('a.sort','asc')->select();
        $this->assign('data',$data);
        return $this->fetch();
    }
    public function schedulingsetform(){
        $data = $this->request->post();
        foreach ($data['id'] as $key => $val){
            $tmp = [];
            if(isset($data['isShow'][$key])){
                $tmp = array_merge($tmp,['isShow' => $data['isShow'][$key]]);
            }
            if(isset($data['sort'][$key])){
                $tmp = array_merge($tmp,['sort' => $data['sort'][$key]]);
            }
            if(isset($data['className'][$key])){
                $tmp = array_merge($tmp,['className' => $data['className'][$key]]);
            }
            if(isset($data['workShift'][$key])){
                $tmp = array_merge($tmp,['workShift' => $data['workShift'][$key]]);
            }
            if(isset($data['closingTime'][$key])){
                $tmp = array_merge($tmp,['closingTime' => $data['closingTime'][$key]]);
            }
            if(isset($data['workLong'][$key])){
                $tmp = array_merge($tmp,['workLong' => $data['workLong'][$key]]);
            }
            if(isset($data['isNormalShift'][$key])){
                $tmp = array_merge($tmp,['isNormalShift' => $data['isNormalShift'][$key]]);
            }
            if(isset($data['des'][$key])){
                $tmp = array_merge($tmp,['des' => $data['des'][$key]]);
            }
            if(!isset($tmp['isShow'])) $tmp['isShow'] = 0;
            if($tmp['className'] == '休息') $tmp['isShow'] = 1;
            $tmp['lastEditDate'] = date('Y-m-d H:i:s');
            $tmp['lastEditUser_id'] = session('employee_id');
            Db::table('bx_guide_scheduling_set')->where('id',$data['id'][$key])->update($tmp);
        }
        $this->redirect('Ashippingterminal/schedulingset');

    }
}